Imports System.Data.SqlClient

Public MustInherit Class DABase
    Implements ITransaction

#Region "   Implements ITransaction methods"
    Sub BeginTrans() Implements ITransaction.BeginTrans
        If Me.Connection Is Nothing Then
            Me.Connection = Me.GetConnection
            m_OwnedConnection = True
        End If

        If Me.Connection.State <> ConnectionState.Open Then
            Me.Connection.Open()
        End If

        m_Transaction = CType(Me.Connection.BeginTransaction, SqlTransaction)
        m_OwnedTransaction = True
    End Sub

    Sub CommitTrans() Implements ITransaction.CommitTrans
        Me.Transaction.Commit()
        m_OwnedTransaction = False
        Me.DisposeConnection()
    End Sub

    Sub RollBackTrans() Implements ITransaction.RollBackTrans
        Try
            Me.Transaction.Rollback()
            m_OwnedTransaction = False
        Catch
        End Try
        Me.DisposeConnection()
    End Sub

    Property Connection() As IDbConnection Implements ITransaction.Connection
        Get
            If m_Connection Is Nothing Then
                Me.InitConnection()
            End If
            Return m_Connection
        End Get
        Set(ByVal Value As IDbConnection)
            m_Connection = CType(Value, SqlConnection)
        End Set
    End Property

    Property Transaction() As IDbTransaction Implements ITransaction.Transaction
        Get
            Return m_Transaction
        End Get
        Set(ByVal Value As IDbTransaction)
            m_Transaction = CType(Value, SqlTransaction)
            m_Connection = CType(Value, SqlTransaction).Connection
            m_OwnedConnection = False
        End Set
    End Property
#End Region

#Region "   Get Commands"

    Protected MustOverride Function getSelectCommand(ByVal keys() As Object) As SqlCommand

    Protected MustOverride Function getInsertCommand() As SqlCommand

    Protected MustOverride Function getUpdateCommand() As SqlCommand

    Protected MustOverride Function getDeleteCommand() As SqlCommand

#End Region

#Region "   Database properties"

    Private m_Connection As SqlConnection
    Private m_Transaction As SqlTransaction
    Private m_OwnedConnection As Boolean
    Private m_OwnedTransaction As Boolean = False

    Public Function GetConnection() As SqlConnection
        Return New SqlConnection(Common.GetConnectionString())
    End Function

    Public Overloads Sub InitConnection()
        InitConnection(Common.GetConnectionString())
    End Sub

    Public Overloads Sub InitConnection(ByVal connectionString As String)
        If m_Connection Is Nothing Then
            m_Connection = New SqlConnection(connectionString)
            m_OwnedConnection = True
        End If
    End Sub

    Protected Sub DisposeConnection()
        If m_OwnedConnection Then
            If Not m_OwnedTransaction Then
                If Not (m_Connection Is Nothing) Then
                    m_Connection.Dispose()
                    m_Connection = Nothing
                    m_OwnedConnection = False
                End If
            End If            
        End If
    End Sub

#End Region

#Region "   Database commands"
    Overloads Sub [Select](ByVal ID As Object, ByRef ds As DataSet)
        [Select](ID, ds, ds.Tables(0).TableName)
    End Sub

    Overloads Sub [Select](ByVal keys() As Object, ByRef ds As DataSet, ByVal tableName As String)
        InitConnection()
        Me.SelectBase(keys, ds, tableName)
        DisposeConnection()
    End Sub

    Protected Overridable Sub SelectBase(ByVal keys() As Object, ByRef ds As DataSet, ByVal tableName As String)
        Dim selectCommand As SqlCommand = Me.getSelectCommand(keys)

        Dim da As New SqlDataAdapter(selectCommand)

        da.Fill(ds, tableName)
    End Sub

#Region "   Update Command"
    Overloads Sub Update(ByRef ds As DataSet, ByVal tableName As String)

        InitConnection()

        UpdateDataSet(ds, tableName)

        DisposeConnection()
    End Sub

#End Region

#Region "   Update DataSet Command"
    Public Overloads Sub UpdateDataSet(ByVal ds As DataSet)
        UpdateDataSet(ds, ds.Tables(0).TableName)
    End Sub

    Public Overloads Function UpdateDataSet(ByVal ds As DataSet, ByVal tableName As String) As Integer
        Dim da As New SqlDataAdapter
        Dim iRowAfected As Integer
        With da
            .InsertCommand = getInsertCommand()
            .UpdateCommand = getUpdateCommand()
            .DeleteCommand = getDeleteCommand()

            Try
                iRowAfected = .Update(ds, tableName)
            Catch ex As SqlException
                Select Case ex.Number
                    Case Constants.ERR_NUMBER_DUPLICATE_KEY, Constants.ERR_NUMBER_DATA_ENTRY
                        Throw New DataEntryException(ex.Message, ex)
                    Case Else
                        Throw ex
                End Select            
            End Try

        End With

        Return iRowAfected
    End Function

#End Region

#End Region

End Class
